package com.mms.dao;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;






import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Configure;
import com.mms.entity.ConfigureId;
import com.mms.entity.Invitation;
import com.mms.entity.InvitationId;
import com.mms.entity.Meeting;
import com.mms.entity.MeetingRoom;
import com.mms.entity.Staff;
import com.mms.util.HibernateUtil;

public class MeetingManagementDao {
    
   
public List<Meeting> getMeetingMes(int Spage){
		Session session = HibernateUtil.getSession();
		List<Object[]> objs;
		List<Meeting> meetings = new ArrayList<Meeting>();
		try{
			String sql = "select * from meeting,meeting_room where meeting.mr_id=meeting_room.mr_id limit ?,6";
			Transaction transaction = session.beginTransaction();
			Query query = session.createSQLQuery(sql);
			query.setInteger(0,Spage*6);
			objs = query.list();
			for(Object[] objects : objs){
				Meeting meeting = new Meeting();
				MeetingRoom meetingRoom = new MeetingRoom();
				Staff staff = new Staff();
				meetingRoom.setMrId(Integer.parseInt(objects[0].toString()));
				meetingRoom.setCapacity(Short.parseShort(objects[9].toString()));
				meetingRoom.setMrName(objects[11].toString());
				staff.setSId(Integer.parseInt(objects[1].toString()));
				meeting.setMeetingRoom(meetingRoom);
				meeting.setStaff(staff);
				meeting.setTopic(objects[2].toString());
				meeting.setState(Integer.parseInt(objects[3].toString()));
				meeting.setStartTime(Timestamp.valueOf(objects[4].toString()));
				meeting.setEndTime(Timestamp.valueOf(objects[5].toString()));
				meeting.setContent(objects[6].toString());
				if(objects[7]==null){
					meeting.setReason("");
				}else{
				meeting.setReason(objects[7].toString());
				}
				meetings.add(meeting);
			}
			transaction.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return meetings;
	}
	
	public void deleteMeeting(Meeting meeting){
			Session session = HibernateUtil.getSession();
		try {
			Transaction transaction = session.beginTransaction();
			session.delete(meeting);
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}finally{
			session.close();
		}
	}
	
	public int getcount(){
		  Session session = HibernateUtil.getSession();
		  List<BigInteger> num;
		  int nums = 0;
		 try{
			 Transaction transaction = session.beginTransaction();
			 String sql = "select count(topic) from meeting";
			 Query query = session.createSQLQuery(sql);
			 num = query.list();
			 nums = Integer.parseInt(num.get(0).toString());
			 transaction.commit();
		 }catch(Exception e){
			 e.printStackTrace();
		 }finally{
			 session.close();
		 }
		 return nums;
	 }
	
	/*
	public boolean isInvitationExist(Invitation invitation){
        boolean isExist = false;
        Connection conn = new dbConnection().getconn(); 
           try {
               String sql = "select count(*) from invitation where s_id = ? and topic = ?";
               PreparedStatement pStatement = conn.prepareStatement(sql);
              
               pStatement.setInt(1,invitation.getsID());
               pStatement.setString(2,invitation.getTopic());
               System.out.println(pStatement.toString());
               ResultSet rSet=pStatement.executeQuery();
               if (rSet.next()) {
                   if (rSet.getInt("count(*)")>0) {
                       isExist=true;
                   }
               }
               pStatement.close();
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
          }
           return isExist;
   }
	
	public boolean addInvitation(Invitation invitation){
        boolean isSuccess = false;
        Connection conn = new dbConnection().getconn(); 
           try {
               String sql = "insert into invitation(s_id,topic) values(?,?)";
               PreparedStatement pStatement = conn.prepareStatement(sql);
               pStatement.setInt(1,invitation.getsID());
               pStatement.setString(2,invitation.getTopic());
               int row = pStatement.executeUpdate();
               if(row > 0){
                   isSuccess = true;
               }
               pStatement.close();
               conn.close();
           } catch (SQLException e) {
               e.printStackTrace();
          }
           return isSuccess;
   }*/
	
	public List<Meeting> getSearchMeetingMes(String searchMes){
		Session session = HibernateUtil.getSession();
		List<Object[]> objs;
        List<Meeting> meetings = new ArrayList<Meeting>();
        try{
            String sql = "select * from meeting,meeting_room where meeting.mr_id=meeting_room.mr_id and topic like '%"+searchMes+"%'";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            objs = query.list();
            for(Object[] objects : objs){
            	Meeting meeting = new Meeting();
				MeetingRoom meetingRoom = new MeetingRoom();
				Staff staff = new Staff();
				meetingRoom.setMrId(Integer.parseInt(objects[0].toString()));
				meetingRoom.setCapacity(Short.parseShort(objects[9].toString()));
				meetingRoom.setMrName(objects[11].toString());
				staff.setSId(Integer.parseInt(objects[1].toString()));
				meeting.setMeetingRoom(meetingRoom);
				meeting.setStaff(staff);
				meeting.setTopic(objects[2].toString());
				meeting.setState(Integer.parseInt(objects[3].toString()));
				meeting.setStartTime(Timestamp.valueOf(objects[4].toString()));
				meeting.setEndTime(Timestamp.valueOf(objects[5].toString()));
				meeting.setContent(objects[6].toString());
				if(objects[7]==null){
					meeting.setReason("");
				}else{
				meeting.setReason(objects[7].toString());
				}
				meetings.add(meeting);
            }
            transaction.commit();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return meetings;
    }
	
	public List<InvitationId> getInvitions(){
        Session session = HibernateUtil.getSession();
        List<Object[]> objs;
        List<InvitationId> invitations = new ArrayList<InvitationId>();
        try{
            String sql = "select s_name,topic from invitation natural join staff";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            objs = query.list();
            for(Object[] objects : objs){
            	InvitationId invitation = new InvitationId();
            	Meeting meeting = new Meeting();
            	Staff staff = new Staff();
            	meeting.setTopic(objects[1].toString());
                invitation.setMeeting(meeting);
                staff.setSName(objects[0].toString());
                invitation.setStaff(staff);
                invitations.add(invitation);
            }
            transaction.commit();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return invitations;
    }
	
}
